<?
session_start();
error_reporting(E_ALL);
include('conn.php');

$ch = $_REQUEST['q'];
if($ch=='2'){
	$qry1="SELECT distinct custid from opbalmast;";
	$qry = mysql_query($qry1);
	$data = array();
	$i = 0;
	while($rows = mysql_fetch_array($qry)){
		$qry2="SELECT custdesc from customermast where custid='".$rows['custid']."' ";
		$rows2 = mysql_fetch_array(mysql_query($qry2));
		$data[$i]=$rows2['custdesc'];
		$i++;
	}
	echo json_encode($data);
}
else{
//$custid = $_SESSION['custid'];
//$custid = 484; // rajesh- test
$custid = 423; // AKBAR ONLINE
$selc = $_REQUEST['selc'];
$frdate = $_REQUEST['from'];
$todate = $_REQUEST['to'];
$qry1="SELECT custid from customermast where custdesc ='$selc';";
$qry = mysql_query($qry1);
$rows = mysql_fetch_array($qry);
$custid=$rows['custid'];

/// ########################       added 18th Jan
 $qry1="select t1.custid,t1.opbaldate,t1.running_total from 
(
SELECT n.custid,n.opbaldate, SUM(o.amount) As running_total FROM (
 select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	 (
		 select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where status =0 and venid3 > 0  and netrate3 > 0
		 and trndate between '$frdate' and '$todate'
		 union all
		 select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		 union all
		 select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		 union all
		 (select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		 union all
		 (select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	 ) tt
	 group by custid,opbaldate
	 order by custid,opbaldate
 ) n INNER JOIN 
 (
 select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	 (
		 select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where  status =0 and venid3 > 0  and netrate3 > 0
		
		 and trndate between '$frdate' and '$todate'
		 union all
		 select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where  status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		 union all
		 select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		 union all
		 (select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		 union all
		 (select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	 ) tt
	 group by custid,opbaldate
	 order by custid,opbaldate
 )
 o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) 
 GROUP BY n.custid, n.opbaldate
 order by n.custid, n.opbaldate
) t1
join 
(
select tt1.custid,max(tt1.opbaldate) opbaldate
from 
(
SELECT n.custid,n.opbaldate, SUM(o.amount) As running_total FROM (
 select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	 (
		 select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where status =0 and venid3 > 0  and netrate3 > 0
		 and trndate between '$frdate' and '$todate'
		 union all
		 select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		 union all
		 select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		 union all
		 (select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		 union all
		 (select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	 ) tt
	 group by custid,opbaldate
	 order by custid,opbaldate
 ) n INNER JOIN 
 (
 select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	 (
		 select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where  status =0 and venid3 > 0  and netrate3 > 0
		
		 and trndate between '$frdate' and '$todate'
		 union all
		 select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where  status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		 union all
		 select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		 union all
		 (select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		 union all
		 (select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	 ) tt
	 group by custid,opbaldate
	 order by custid,opbaldate
 )
 o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) 
 GROUP BY n.custid, n.opbaldate
 order by n.custid, n.opbaldate
 ) tt1
group by tt1.custid
 ) t2
 on t1.custid = t2.custid
 and t1.opbaldate = t2.opbaldate
 and t1.custid in (SELECT distinct custid from opbalmast)
 order by t1.custid ";

	$qry = mysql_query($qry1);
	$data = array();
	$i=1;	
	
	while($rows = mysql_fetch_array($qry))
	{
		$qrycust = "select custdesc from customermast where custid='".$rows['custid']."';";
		$rowcust = mysql_fetch_array(mysql_query($qrycust));
		
		$data[] = array("Sr_NO" => $i++,        			
						"rqdate" => $rows['opbaldate'],
						"custid" => $rows['custid'],
						"customer" => $rowcust['custdesc'],
						"runningtotal" =>$rows['running_total']
						);
	}
	echo json_encode($data);
}
?>